<?php if (!defined('BASEPATH')) exit('No direct script access allowed');

/**
 * 共用模块-临时文件上传的Ajax调用接口
 */
class ExcelExport extends AdminController
{
    function __construct()
    {
        parent::__construct();
    }

    /**
     * python 导出数据表
     */
    public function python_export()
    {
        $param = $this->input->post();
        if ($param['page_name']) {
            $where = $this->session->userdata($param['page_name']);
        } else {
            $where = '';
            $id = $param['pay_request_id'];
            unset($param['pay_request_id']);
        }

        //根据所请求的表格类型查询数据，开始赋值
        switch ($param['excel_type']) {
            case 'FinanceApply':
                $ids = $param['request_ids'];
                $this->load->model('MTaskRecordItem');
                $this->MTaskRecordItem->getListByRequestIds($ids);
                break;
            case 'TaskExport':
                $this->load->model('MTask');
                $this->MTask->exportList($where);
                break;
            case 'TaskOperateExport':
                $this->load->model('MTaskOperate');
                $this->MTaskOperate->exportList($where);
                break;
            case 'TaskRecordItemExport':
                $this->load->model('MTaskRecordItem');
                $this->MTaskRecordItem->exportList($where);
                break;
            case 'PayHistoryExport':
                $this->load->model('MPayHistory');
                $this->MPayHistory->exportList($where);
                break;
            case 'PayTaskRecordItemExport':
                $this->load->model('MTaskRecordItem');
                $this->MTaskRecordItem->exportPayList($id);
                break;
            case 'MiddleOperate':
                $this->load->model('MTaskRecordItem');
                $this->MTaskRecordItem->getListByIds($this->session->userdata('PAY_REQUEST_CEPING_IDS'));
                break;
        }
        $sql = $this->db->last_query();
        $table = str_replace("`", "", $sql);

        $path = 'python3 php.py "'.$table.'" '.$param['excel_type'].' '.implode(',',$param['field']).' 2>&1';

        exec($path, $out, $res);
        if ($res == 0){
            exit(json_encode([
                'status' => 1,
                'msg' => '导出成功',
                'url' => $out[0]
            ]));
        }else{
            exit(json_encode([
                'status' => 0,
                'msg' => '导出失败',
                'data' => $out
            ]));
        }
    }

    /**
     * @throws PHPExcel_Exception
     * @throws PHPExcel_Reader_Exception
     */
    public function excel_export()
    {
        set_time_limit(0);
        ini_set("memory_limit", "1024M");

        $param = $this->input->post();
        if ($param['page_name']) {
            $where = $this->session->userdata($param['page_name']);
        } else {
            $where = '';
            $id = $param['pay_request_id'];
            unset($param['pay_request_id']);
        }

        $file_title = $param['file_name'];
        // 获取要生成的表格列值
        if (!$param['field']) {
            error('请选择要生成的字段');
        }
        // 根据选择的字段数，选择表头长度
        $head_length = count($param['field']);
        //生成列名，A-Z  AA-AZ  BA-BZ  CA-CT
        $head_total = range("A", "Z");
        foreach (range("A", "Z") as $val) {
            array_push($head_total, 'A' . $val);
        }
        foreach (range("A", "Z") as $val) {
            array_push($head_total, 'B' . $val);
        }
        foreach (range("A", "Z") as $val) {
            array_push($head_total, 'C' . $val);
        }

        $letter = array_slice($head_total, 0, $head_length);
        $end_letter = $head_total[$head_length - 1];

        $this->load->library('PHPExcel');
        $this->load->library('PHPExcel/IOFactory');

        //数据缓存
        $cacheMethod = PHPExcel_CachedObjectStorageFactory:: cache_to_discISAM;
        PHPExcel_Settings::setCacheStorageMethod($cacheMethod);

        //引入Excel类
        $objPHPExcel = new \PHPExcel();

        // 设置excel文档的属性
        $objPHPExcel->getProperties()->setCreator("cyf")
            ->setLastModifiedBy("Amazon Excel")
            ->setTitle("Amazon")
            ->setSubject("Amazon")
            ->setDescription("Amazon")
            ->setKeywords("Amazon")
            ->setCategory("Amazon result file");
        //设置excel工作表名及文件名
        $excel_filename = $file_title . date('Ymd_His');

        //根据所请求的表格类型查询数据，开始赋值
        switch ($param['excel_type']) {
            case 'FinanceApply':
                $ids = $param['request_ids'];
                $this->load->model('MTaskRecordItem');
                $data = array();
                $list = (array)$this->MTaskRecordItem->getListByRequestIds($ids);
                break;
            case 'TaskExport':
                $this->load->model('MTask');
                $data = array();
                $list = (array)$this->MTask->exportList($where);
                break;
            case 'TaskOperateExport':
                $this->load->model('MTaskOperate');
                $data = array();
                $list = (array)$this->MTaskOperate->exportList($where);
                break;
            case 'TaskRecordItemExport':
                $this->load->model('MTaskRecordItem');
                $data = array();
                $list = (array)$this->MTaskRecordItem->exportList($where);
                break;
            case 'PayHistoryExport':
                $this->load->model('MPayHistory');
                $data = array();
                $list = (array)$this->MPayHistory->exportList($where);
                break;
            case 'PayTaskRecordItemExport':
                $this->load->model('MTaskRecordItem');
                $data = array();
                $list = (array)$this->MTaskRecordItem->exportPayList($id);
                break;
            case 'MiddleOperate':
                $this->load->model('MTaskRecordItem');
                $data = array();
                $list = (array)$this->MTaskRecordItem->getListByIds($this->session->userdata('PAY_REQUEST_CEPING_IDS'));
                break;
        }
        //修改list数据结构
        foreach ($list as $k => $v) {
            $item = [];
            foreach ($param['field'] as $new_v) {

                $item[] = $v->$new_v;
            }
            $data[] = $item;
        }

        $length = ceil(count($list) / 50);

        for ($x = 0; $x < $length; $x++) {

            if ($x > 0) {
                $objPHPExcel->createSheet();
            }

            // 操作第一个工作表
            $objPHPExcel->setActiveSheetIndex($x);

            //设置填充的样式和背景色
            $objPHPExcel->getActiveSheet()->getStyle('A1:' . $end_letter . '1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
            $objPHPExcel->getActiveSheet()->getStyle('A1:' . $end_letter . '1')->getFill()->getStartColor()->setARGB('E62E8B57');
            $objPHPExcel->getActiveSheet()->getStyle('A1:' . $end_letter . '1')->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_WHITE);

            //设置单元格内容加粗
            $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
            //设置单元格内容水平居中
            $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            //设置excel的表头
            $sheet_title = IOSS_StaticConfig::translateField($param['field']);
//        $sheet_title = $param['field'];
            // 设置第一行和第一行的行高
            $objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(20);
            $objPHPExcel->getActiveSheet()->getRowDimension('2')->setRowHeight(25);

            //设置单元格
            $objPHPExcel->getActiveSheet()->getStyle('A1:' . $end_letter . '1')->getBorders()->getAllBorders()->setBorderStyle(\PHPExcel_Style_Border::BORDER_HAIR);

            //首先是赋值表头
            for ($k = 0; $k < count($sheet_title); $k++) {
                $objPHPExcel->getActiveSheet()->setCellValue($letter[$k] . '1', $sheet_title[$k]);
                $objPHPExcel->getActiveSheet()->getStyle($letter[$k] . '1')->getFont()->setSize(11)->setBold(true);
                //设置单元格内容水平居中
                $objPHPExcel->getActiveSheet()->getStyle($letter[$k] . '1')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
                //设置每一列的宽度
                $objPHPExcel->getActiveSheet()->getColumnDimension($letter[$k])->setWidth(20);
            }

            //循环插入行
            for ($i = 50 * $x; $i < 50 * ($x + 1); $i++) {
                //先确定行
                $row = $i - ($x * 50) + 2;
                //开始为每个单元格赋值
                $value = $data[$i];

                // 设置行高
                $objPHPExcel->getActiveSheet()->getRowDimension($row)->setRowHeight(21);

                //循环列
                for ($j = 0; $j < $head_length; $j++) {
                    $cellValue = $value[$j];
                    if (is_numeric($cellValue) && $cellValue == 0) {
                        $cellValue = '';
                    }
                    $suffix = $this->get_extension($cellValue);
                    //赋值(图片另处理)
                    if (strstr($cellValue, 'upload/') && !strstr($suffix, 'doc') && !strstr($suffix, 'xls') && !strstr($suffix, 'zip') && !strstr($suffix, 'rar')) {
                        //导出数量大于500时，显示图片HTTP链接
//                        if (count($data) < 51) {
//                            $path = iconv('UTF-8', 'GB2312', './' . $cellValue);
//                            if (file_exists($path)) {
//                                // 图片生成
//                                $objDrawing[$j] = new \PHPExcel_Worksheet_Drawing();
//                                $objDrawing[$j]->setPath($path);
//
//                                // 设置宽度高度
//                                $objDrawing[$j]->setHeight(80);//照片高度
//                                $objDrawing[$j]->setWidth(80); //照片宽度
//
//                                /*设置图片要插入的单元格*/
//                                $objDrawing[$j]->setCoordinates($letter[$j] . $row);
//                                // 图片偏移距离
//                                $objDrawing[$j]->setOffsetX(20);
//                                $objDrawing[$j]->setOffsetY(20);
//                                $objDrawing[$j]->setWorksheet($objPHPExcel->getActiveSheet());
//
//                                // 设置行高
//                                $objPHPExcel->getActiveSheet()->getRowDimension($row)->setRowHeight(80);
//                            } else {
//                                $objPHPExcel->getActiveSheet()->setCellValue($letter[$j] . $row, '图片丢失');
//                            }
//                        } else {
//                            $cellValue = 'http://' . $_SERVER['HTTP_HOST'] . '/' . $cellValue;
//                            $objPHPExcel->getActiveSheet()->setCellValue($letter[$j] . $row, $cellValue);
//                        }
                        $path = iconv('UTF-8', 'GB2312', './' . $cellValue);
                        if (file_exists($path)) {
                            // 图片生成
                            $objDrawing[$j] = new \PHPExcel_Worksheet_Drawing();
                            $objDrawing[$j]->setPath($path);

                            // 设置宽度高度
                            $objDrawing[$j]->setHeight(80);//照片高度
                            $objDrawing[$j]->setWidth(80); //照片宽度

                            /*设置图片要插入的单元格*/
                            $objDrawing[$j]->setCoordinates($letter[$j] . $row);
                            // 图片偏移距离
                            $objDrawing[$j]->setOffsetX(20);
                            $objDrawing[$j]->setOffsetY(20);
                            $objDrawing[$j]->setWorksheet($objPHPExcel->getActiveSheet());

                            // 设置行高
                            $objPHPExcel->getActiveSheet()->getRowDimension($row)->setRowHeight(80);
                        } else {
                            $objPHPExcel->getActiveSheet()->setCellValue($letter[$j] . $row, '图片丢失');
                        }
                    } else {
                        if (strstr($cellValue, 'upload/') && (strstr($suffix, 'doc') || strstr($suffix, 'xls') || strstr($suffix, 'zip') || strstr($suffix, 'rar'))) {

                            $cellValue = 'http://' . $_SERVER['HTTP_HOST'] . '/' . $cellValue;
                            $objPHPExcel->getActiveSheet()->setCellValue($letter[$j] . $row, $cellValue);
                        } else {
                            $objPHPExcel->getActiveSheet()->setCellValueExplicit($letter[$j] . $row, $cellValue, PHPExcel_Cell_DataType::TYPE_STRING2);
                        }

                    }
                    unset($cellValue);

                    //设置字体大小
                    $objPHPExcel->getActiveSheet()->getStyle($letter[$j] . $row)->getFont()->setSize(11);
                    //设置单元格内容水平居中
                    $objPHPExcel->getActiveSheet()->getStyle($letter[$j] . $row)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
                    //设置自动换行
                    $objPHPExcel->getActiveSheet()->getStyle($letter[$j] . $row)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER); // 垂直方向上中间居中
                    $objPHPExcel->getActiveSheet()->getStyle($letter[$j] . $row)->getAlignment()->setWrapText(true); // 自动换行
                }
            };
            $objPHPExcel->getActiveSheet()->setTitle('sheet' . $x);
            //赋值结束，开始输出
            header('Content-Type: application/vnd.ms-excel');
            header('Content-Disposition: attachment;filename="' . $excel_filename . '.xls"');
            header('Cache-Control: max-age=0');
        }

        //返回已经存好的文件目录地址提供下载
        $objWriter = IOFactory::createWriter($objPHPExcel, 'Excel5');
        $response = array(
            'status' => 1,
            'msg' => '导出成功',
            'url' => $this->saveExcelToLocalFile($objWriter, $excel_filename)
        );
        unset($objPHPExcel);
        unset($objWriter);
        exit(json_encode($response));
    }

    /**
     * 生成xls文件并存入当前文件目录
     * @param $objWriter
     * @param $excel_filename
     * @return string
     */
    function saveExcelToLocalFile($objWriter, $excel_filename)
    {
        if (!is_dir('./upload/excelfiles')) {
            mkdir('./upload/excelfiles');
        }
        $filePath = './upload/excelfiles/' . $excel_filename . '.xls';
        $objWriter->save($filePath);
        return $filePath;
    }

    function get_extension($filename)
    {
        $x = explode('.', $filename);
        return '.' . end($x);
    }

    /**
     * 清除本地导出文件
     */
    public function clear_local_file(){
        $this->delFile('./upload/excelfiles/');
        $this->delFile('./upload/test/');
        $this->delFile('./upload/import_excel/');
        $this->delFile('./upload/import_order_excel/');
    }

    /**
     * delFile删除目录下的文件
     */
    private function delFile($path)
    {
        if(is_dir($path)){
            //扫描一个文件夹内的所有文件夹和文件并返回数组
            $p = scandir($path);
            foreach($p as $val){
                //排除目录中的.和..
                if($val !="." && $val !=".."){
                    //如果是目录则递归子目录，继续操作
                    if(is_dir($path.$val)){
                        //子目录中操作删除文件夹和文件
                        $this->delFile($path.$val.'/');
                        //目录清空后删除空文件夹
                        @rmdir($path.$val.'/');
                    }else{
                        //如果是文件直接删除
                        unlink($path.$val);
                    }
                }
            }
            return true;
        }else{
            return false;
        }
    }
}
